package datos.clases;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import tipos.clases.Usuario;
import datos.interfaces.*;


public class JDBCLoginDAO implements ILoginDAO{

	public JDBCLoginDAO(){
		
	}
	
public void registra(Usuario u) {
		
		Connection c = ConnectionManager.getInstance().checkOut();		
		PreparedStatement stmt = null;
            
        String sql = "INSERT INTO Usuarios(Alias,Password,Dni,Nombre,Apellidos,Email,Telefono,Tarjeta) VALUES (?,?,?,?,?,?,?,?)";	
        
        try {
        	
            stmt = c.prepareStatement(sql);
            stmt.setString(1, u.getAlias());
            stmt.setString(2, u.getPassword());
            stmt.setString(3, u.getDni());
            stmt.setString(4, u.getNombre());
            stmt.setString(5, u.getApellidos());
            stmt.setString(6, u.getEmail());
            stmt.setString(7, u.getTelefono());
            stmt.setString(8, u.getTarjeta());
            
            stmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        
	}

public void elimina(Usuario u) {
	
	Connection c = ConnectionManager.getInstance().checkOut();		
	PreparedStatement stmt = null;
        
    String sql = "DELETE From Usuarios Where Alias = ?";	
    
    try {    	
        stmt = c.prepareStatement(sql);
        stmt.setString(1,u.getAlias());        
        stmt.executeUpdate();

    } catch (SQLException e) {
        System.out.println("Message: " + e.getMessage());
        System.out.println("SQLState: " + e.getSQLState());
        System.out.println("ErrorCode: " + e.getErrorCode());
    } finally {
        try {
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException e) {
        }
    }
    
}

public void actualiza(Usuario u){
	Connection c = ConnectionManager.getInstance().checkOut();		
	PreparedStatement stmt = null;
	
	String sql = "UPDATE Usuarios SET Alias = ?, Password = ?, Nombre= ?, Apellidos=?, Dni=?, Telefono=?, Email=? WHERE(Alias = ?)";
	try {
    	
        stmt = c.prepareStatement(sql);
        stmt.setString(1, u.getAlias());
        stmt.setString(2, u.getPassword());
        stmt.setString(3, u.getNombre());
        stmt.setString(4, u.getApellidos());   
        stmt.setString(5, u.getDni());    
        stmt.setString(6, u.getTelefono());   
        stmt.setString(7, u.getEmail());    
        stmt.setString(8, u.getAlias());    
        stmt.executeUpdate();

    } catch (SQLException e) {
        System.out.println("Message: " + e.getMessage());
        System.out.println("SQLState: " + e.getSQLState());
        System.out.println("ErrorCode: " + e.getErrorCode());
    } finally {
        try {
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException e) {
        }
    }
     
}

	
	public boolean existe(String user, String pass){
		Connection c = ConnectionManager.getInstance().checkOut();
		boolean res=false;
		PreparedStatement stmt = null;
        ResultSet result = null;        
        String sql = "SELECT * FROM Usuarios WHERE (Alias = ? && Password = ?) ";
        
        try{
        	if(c==null){
        		System.out.println("No hay conexion");
        	}
        	stmt = c.prepareStatement(sql);
            stmt.setString(1,user);
            stmt.setString(2,pass);
            result = stmt.executeQuery(); 
            if(result.next()){
            	res=true;
            }            
        }catch(SQLException e){
        	System.out.println("Error");
        }finally{
        	  try {
                  if (result != null) {
                      result.close();
                  }
                  if (stmt != null) {
                      stmt.close();
                  }
              } catch (SQLException e) {
              }
        }
		return res;
	}
	
	public List select(){
		Connection c = ConnectionManager.getInstance().checkOut();
		List res = new LinkedList();
		PreparedStatement stmt = null;
        ResultSet result = null;        
        String sql = "SELECT * FROM Usuarios";
        
        try{
        	if(c==null){
        		System.out.println("No hay conexion");
        	}
        	stmt = c.prepareStatement(sql);      
        	stmt.executeQuery(sql);
            result = stmt.executeQuery();    
            while (result.next()) {
                Usuario temp = new Usuario();               
                temp.setAlias(result.getString("Alias"));
                temp.setPassword(result.getString("Password"));
                temp.setDni(result.getString("Dni"));
                temp.setNombre(result.getString("Nombre"));
                temp.setApellidos(result.getString("Apellidos"));
                temp.setTelefono(result.getString("Telefono"));
                temp.setEmail(result.getString("Email"));
                temp.setTarjeta(result.getString("Tarjeta"));       
                res.add(temp);
            }
            
        }catch(SQLException e){
        	System.out.println("Error");
        }finally{
        	  try {
                  if (result != null) {
                      result.close();
                  }
                  if (stmt != null) {
                      stmt.close();
                  }
              } catch (SQLException e) {
              }
        }
		return res;
	}
	
	public Usuario select(String alias){
		Connection c = ConnectionManager.getInstance().checkOut();
		Usuario res = new Usuario();
		PreparedStatement stmt = null;
        ResultSet result = null;        
        String sql = "SELECT * FROM Usuarios WHERE (Alias = ? )";
        
        try{
        	if(c==null){
        		System.out.println("No hay conexion");
        	}
        	stmt = c.prepareStatement(sql);
        	stmt.setString(1,alias);        	
            result = stmt.executeQuery();    
            
            result.next();                              
                res.setAlias(result.getString("Alias"));
                res.setPassword(result.getString("Password"));
                res.setDni(result.getString("Dni"));
                res.setNombre(result.getString("Nombre"));
                res.setApellidos(result.getString("Apellidos"));
                res.setTelefono(result.getString("Telefono"));
                res.setEmail(result.getString("Email"));
                res.setTarjeta(result.getString("Tarjeta"));                  
            
            
        }catch(SQLException e){
        	System.out.println("Error");
        	e.printStackTrace();
        }finally{
        	  try {
                  if (result != null) {
                      result.close();
                  }
                  if (stmt != null) {
                      stmt.close();
                  }
              } catch (SQLException e) {
              }
        }
		return res;
	}
	
	public void registraTarjeta(Usuario u){		
		Connection c = ConnectionManager.getInstance().checkOut();		
		PreparedStatement stmt = null;
            
        String sql = "UPDATE Usuarios SET Tarjeta= ? WHERE(Alias = ?)";	
        
        try {
        	
            stmt = c.prepareStatement(sql);
            stmt.setString(1, u.getTarjeta());
            stmt.setString(2, u.getAlias());                   
            stmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        
	}
	
	public void eliminaTarjeta(String alias) {
		
		Connection c = ConnectionManager.getInstance().checkOut();		
		PreparedStatement stmt = null;
	        
	    String sql = "UPDATE Usuarios SET Tarjeta = ? WHERE Alias = ?";	
	    
	    try {
	    	
	        stmt = c.prepareStatement(sql);
	        stmt.setString(1,null);    
	        stmt.setString(2,alias);
	        stmt.executeUpdate();

	    } catch (SQLException e) {
	        System.out.println("Message: " + e.getMessage());
	        System.out.println("SQLState: " + e.getSQLState());
	        System.out.println("ErrorCode: " + e.getErrorCode());
	    } finally {
	        try {
	            if (stmt != null) {
	                stmt.close();
	            }
	        } catch (SQLException e) {
	        }
	    }
	    
	}
	
	

}
